﻿Imports Microsoft.Office.Interop.Excel

Module ExcelOps
    Public Function getColumn(ByRef ws As Worksheet, column As Integer, Optional rows As Integer = -1, Optional startrow As Integer = 1) As Range
        If (rows = -1) Then
            rows = ws.Columns(1).CurrentRegion.Rows.Count
        End If
        getColumn = ws.Range(ws.Cells(startrow, column), ws.Cells(rows, column))
    End Function
    Public Sub splitColumnInTwo(ByRef Xl As Application, ByRef ws As Worksheet, srccol As Integer, regexp As String, count As Integer, dest1 As Integer, Optional dest2 As Integer = -1)
        Dim i As Integer
        Dim src As Object = ExcelOps.getColumn(ws, 2, count).Value
        Dim repl(count) As String

        Dim repl2(count) As String

        For i = 1 To count
            Dim t As String = src(i, 1)
            repl(i - 1) = Trim(System.Text.RegularExpressions.Regex.Match(t, regexp).ToString)
            If dest2 <> -1 Then
                repl2(i - 1) = Trim(System.Text.RegularExpressions.Regex.Replace(t, regexp, "").ToString)
            End If
        Next
        ExcelOps.getColumn(ws, dest1, count).Value = Xl.WorksheetFunction.Transpose(repl)

        If (dest2 <> -1) Then
            ExcelOps.getColumn(ws, dest2, count).Value = Xl.WorksheetFunction.Transpose(repl2)
        End If

    End Sub
    ''' <summary>
    ''' move column to another, rightshitfing original column.
    ''' </summary>
    ''' <param name="res">worksheet to operate on</param>
    ''' <param name="fromcol">from which column</param>
    ''' <param name="tocol">to which column</param>
    ''' <remarks>meant as temporary before switching to true OOP.</remarks>
    Sub moveColumn(res As Worksheet, fromcol As Integer, tocol As Integer)
        res.Columns(fromcol).Cut()
        res.Columns(tocol).Insert(XlInsertShiftDirection.xlShiftToRight)
    End Sub

End Module
